<?php

namespace XWAM\Model;

/**
 * CustomerFieldModel 类
 *
 * @since VER:1.0; DATE:2016-1-15; AUTHOR:SoChishun; EMAIL:14507247@qq.com; DESC:Added.
 */
class CustomerAttrModel extends AppbaseModel {

    protected $tableName = 't_porg_customer_attr';

    /**
     * 新增属性
     * 流程：创建扩展表t_porg_customer_ex$site_id -> 在扩展表新增字段
     * @return type
     * @since VER:1.0; DATE:2016-1-15; AUTHOR:SoChishun; EMAIL:14507247@qq.com; DESC:Added.
     */
    function add_attr() {
        $rules = array(
            array('field_name', 'require', '字段名称无效!', self::EXISTS_VALIDATE, 'regex', self::MODEL_BOTH),
            array('field_name', 'require', '字段名称已存在!', self::MUST_VALIDATE, 'unique', self::MODEL_INSERT),
            array('title', 'require', '属性标题无效!', self::EXISTS_VALIDATE, 'regex', self::MODEL_BOTH),
            array('options_input_type', 'require', '输入类型无效!', self::MUST_VALIDATE, 'regex', self::MODEL_BOTH),
            array('status', 'number', '状态无效!', self::EXISTS_VALIDATE, 'regex', self::MODEL_BOTH),
            array('site_id', 'number', 'SiteID无效!', self::EXISTS_VALIDATE, 'regex', self::MODEL_BOTH),
        );
        if (!$this->validate($rules)->create()) {
            return $this->returnMsg(false, $this->error);
        }
        if (in_array($this->options_input_type, array('select', 'radio', 'checkbox')) && empty($this->options_content)) {
            return $this->returnMsg(false, '配置内容无效!');
        }
        // 检测并创建扩展表 2016-5-11
        $table_ex = 't_porg_customer_s' . $this->site_id;
        $script = 'create table if not exists ' . $table_ex . ' (customer_id int primary key) ENGINE=MyISAM  DEFAULT CHARSET=utf8;';
        $this->execute($script);

        $fieldtype = '';
        switch ($this->options_input_type) {
            case 'int':
                $this->data['field_type'] = 'int';
                $fieldtype = 'int not null default 0';
                break;
            case 'decimal':
                $this->data['field_type'] = 'decimal';
                $this->data['field_length'] = 8;
                $this->data['field_decimals'] = 2;
                $fieldtype = 'decimal(8,2) not null default 0.00';
                break;
            case 'textarea':
            case 'editor':
                $this->data['field_type'] = 'text';
                $fieldtype = 'text not null default \'\'';
                break;
            case 'datetime':
                $this->data['field_type'] = 'datetime';
                $fieldtype = 'datetime not null default 0';
                break;
            case 'date':
                $this->data['field_type'] = 'date';
                $fieldtype = 'date not null default 0';
                break;
            case 'time':
                $this->data['field_type'] = 'time';
                $fieldtype = 'time not null default 0';
                break;
            default:
                $this->data['field_type'] = 'varchar';
                $this->data['field_length'] = '255';
                $fieldtype = 'varchar(255) not null default \'\'';
                break;
        }
        $this->field_script = $field_name . ' ' . $fieldtype;
        $this->is_system = 'N';
        $field_name = $this->field_name;
        $result = $this->add();
        if (false !== $result) {
            // 增加表字段 2016-1-19
            $sql = 'ALTER TABLE `' . $table_ex . '` ADD COLUMN `' . $field_name . '`  ' . $fieldtype . ' AFTER `customer_id`;';
            $result = $this->execute($sql);
            if (!$result) {
                $this->where('id=' . $result)->delete();
            }
        }
        return $this->returnMsg($result);
    }

    /**
     * 判断是否有扩展客户表
     * @param type $site_id
     * @return type
     * @since 1.0 2016-5-11 SoChishun Added.
     */
    function has_extable($site_id) {
        return $this->where(array('site_id' => $site_id, 'is_system' => 'N', 'is_list' => 'Y'))->count() > 0;
    }

    /**
     * 保存编辑属性
     * @return type
     * @since VER:1.0; DATE:2016-1-20; AUTHOR:SoChishun; EMAIL:14507247@qq.com; DESC:Added.
     */
    function save_attr() {
        $rules = array(
            array('title', 'require', '属性标题无效!', self::EXISTS_VALIDATE, 'regex', self::MODEL_BOTH),
            array('status', 'number', '类型无效!', self::EXISTS_VALIDATE, 'regex', self::MODEL_BOTH),
        );
        if (!$this->validate($rules)->create()) {
            return $this->returnMsg(false, $this->error);
        }
        $result = $this->save();
        return $this->returnMsg($result);
    }

    /**
     * 删除属性
     * @param type $id
     * @return type
     * @since VER:1.0; DATE:2016-1-20; AUTHOR:SoChishun; EMAIL:14507247@qq.com; DESC:Added.
     */
    function delete_attr($id = '') {
        $fields = $this->where(array('id' => array('in', $id)))->getField('field_name, site_id');
        if (!$fields) {
            return $this->returnMsg(false, '无记录!');
        }
        $asqls = array();
        $site_id = 0;
        foreach ($fields as $field => $isite_id) {
            $asqls[] = 'DROP COLUMN `' . $field . '`';
            if ($site_id < 1) {
                $site_id = $isite_id;
            }
        }
        $table_ex = 't_porg_customer_s' . $site_id;
        $sql = 'ALTER TABLE `' . $table_ex . '` ' . implode(',', $asqls) . ';';
        $result = $this->execute($sql);
        if (false !== $result) {
            $result = $this->delete($id);
        }
        return $this->returnMsg($result);
    }

    /**
     * 获取客户属性字段
     * @param integer $site_id
     * @param string $status_field 如：is_form, is_list, is_import
     * @return type
     * @since 1.0 2016-5-11 SoChishun Added.
     */
    protected function get_customer_attr_fields($site_id, $status_field) {
        $cache_key = 'cattrmdl_get_customer_' . $status_field . '_fields_s' . $site_id;
        $cache_data = S($cache_key);
        if ($cache_data) {
            return $cache_data;
        }
        $afields_system = $this->where(array('site_id' => $site_id, $status_field => 'Y', 'is_system' => 'Y'))->order('sort, id')->getField('field_name, title');
        $afields_custom = $this->where(array('site_id' => $site_id, $status_field => 'Y', 'is_system' => 'N'))->order('sort, id')->getField('field_name, title');
        $afields = array('system' => $afields_system, 'custom' => $afields_custom);
        S($cache_key, $afields, 15);
        return $afields;
    }

    /**
     * 获取客户搜索字段列表
     * @param type $site_id
     * @return array field_name, title, option_input_type, is_system, class
     * @since VER:1.0; DATE:2016-1-20; AUTHOR:SoChishun; DESC:Added.
     */
    function get_customer_search_fields($site_id) {
        $cache_key = 'cattrmdl_get_customer_is_search_fields_s' . $site_id;
        $cache_data = S($cache_key);
        if ($cache_data) {
            return $cache_data;
        }
        $list = $this->where(array('site_id' => $site_id, 'is_search' => 'Y'))->order('sort, id')->field('field_name, title, options_input_type, is_system')->select();
        $classes = array('date', 'datetime', 'time');
        foreach ($list as &$row) {
            $input_type = $row['options_input_type'];
            $class = in_array($input_type, $classes) ? $input_type . 'picker' : '';
            $row['class'] = $class;
        }
        array_unshift($list, array('field_name' => 'name', 'title' => '姓名', 'option_input' => 'text', 'is_system' => 'Y', 'class' => ''), array('field_name' => 'telphone', 'title' => '电话', 'option_input' => 'text', 'is_system' => 'Y', 'class' => '')
        );
        S($cache_key, $list, 15);
        return $list;
    }

    /**
     * 获取客户必填字段列表
     * @param type $site_id
     * @return array field_name, title, options_input_type, options_content, is_system, class
     * @since VER:1.0; DATE:2016-1-20; AUTHOR:SoChishun; DESC:Added.
     */
    function get_customer_form_fields($site_id) {
        $cache_key = 'cattrmdl_get_customer_is_form_fields_s' . $site_id;
        $cache_data = S($cache_key);
        if ($cache_data) {
            return $cache_data;
        }
        $list = $this->where(array('site_id' => $site_id, 'is_form' => 'Y'))->order('sort, id')->field('field_name, title, options_input_type, options_content, help_text, is_system')->select();
        $classes = array('date', 'datetime', 'time');
        foreach ($list as &$row) {
            $input_type = $row['options_input_type'];
            $class = in_array($input_type, $classes) ? $input_type . 'picker' : '';
            $row['class'] = $class;
        }
        S($cache_key, $list, 15);
        return $list;
    }

    /**
     * 获取客户列表字段
     * @param integer $site_id
     * @param array $static_fields 固定字段
     * @return array
     * @since 1.0 2016-4-11 SoChishun Added.
     */
    function get_customer_list_fields($site_id, $static_fields = array()) {
        // 去除固定字段
        if (!$static_fields) {
            $static_fields = array('id' => '', 'serial_no' => '客户ID', 'name' => '姓名', 'telphone' => '电话', 'sex' => '性别', 'address' => '地址', 'user_name' => '工号');
        }
        $afields = $this->get_customer_attr_fields($site_id, 'is_list');
        $afields['system'] = $afields['system'] ? array_merge($static_fields, $afields['system']) : $static_fields;
        return $afields;
    }

    /**
     * 获取客户导出字段
     * @param integer $site_id
     * @param array $static_fields 固定字段
     * @return array
     * @since 1.0 2016-4-19 SoChishun Added.
     */
    function get_customer_export_fields($site_id, $static_fields = array()) {
        // 去除固定字段
        if (!$static_fields) {
            $static_fields = array('name' => '姓名', 'telphone' => '电话', 'sex' => '性别', 'address' => '地址');
        }
        $afields = $this->get_customer_attr_fields($site_id, 'is_export');
        $afields['system'] = $afields['system'] ? array_merge($static_fields, $afields['system']) : $static_fields;
        return $afields;
    }

    /**
     * 获取客户导入字段
     * @param integer $site_id
     * @param array $static_fields 固定字段
     * @return array
     * @since 1.0 2016-4-19 SoChishun Added.
     */
    function get_customer_import_fields($site_id, $static_fields = array()) {// 去除固定字段
        if (!$static_fields) {
            $static_fields = array('name' => '姓名', 'telphone' => '电话', 'sex' => '性别', 'address' => '地址');
        }
        $afields = $this->get_customer_attr_fields($site_id, 'is_import');
        $afields['system'] = $afields['system'] ? array_merge($static_fields, $afields['system']) : $static_fields;
        return $afields;
    }

    /**
     * 获取客户导入字段脚本
     * @param type $site_id
     * @param array $static_fields
     * @return type
     * @since 1.0 2016-5-11 SoChishun Added.
     */
    function get_customer_import_script($site_id, $static_fields = array()) {
        $where = array('site_id' => $site_id, 'is_import' => 'Y');
        $afields = $this->where($where)->order('sort, id')->getField('field_script', true);
        if (!$static_fields) {
            $static_fields = array(
                '`name` varchar(16) not null default \'\'',
                'telphone varchar(32) not null default \'\'',
                'sex varchar(6) not null default \'\'',
                'address varchar(200) not null default \'\'',
            );
        }
        foreach ($static_fields as $row) {
            $afields[] = $row;
        }
        return $afields;
    }

    /**
     * 更改状态
     * @param type $id
     * @param type $field
     * @param type $status
     * @param type $site_id
     * @return type
     * @since 1.0 2016-5-11 SoChishun Added.
     */
    function change_status($id, $field, $status, $site_id) {
        if (!$field || !$id) {
            return $this->returnMsg(false, '参数有误!');
        }
        $this->clear_cache($site_id, $field);
        $result = $this->where(array('id' => $id))->setField(array($field => $status));
        return $this->returnMsg($result);
    }

    /**
     * 更改排序
     * @param type $id
     * @param type $sort
     * @param type $site_id
     * @return type
     * @since 1.0 2016-5-11 SoChishun Added.
     */
    function change_sort($id, $sort, $site_id) {
        $this->clear_cache($site_id);
        $result = $this->where(array('id' => $id))->setField(array('sort' => $sort));
        return $this->returnMsg($result);
    }

    /**
     * 清理缓存
     * @param type $site_id
     * @param type $key
     * @return type
     * @since 1.0 2016-5-11 SoChishun Added.
     */
    function clear_cache($site_id, $key = '') {
        if ($key) {
            $cache_key = 'cattrmdl_get_customer_' . $key . '_fields_s' . $site_id;
            F($cache_key, null);
            return;
        }
        $cache_keys = array('is_form', 'is_list', 'is_search', 'is_import', 'is_list', 'is_popup');
        foreach ($cache_key as $cache_key) {
            F('cattrmdl_get_customer_' . $cache_key . '_fields_s' . $site_id, null);
        }
    }

    /**
     * 判断字段名称是否存在
     * @param type $field_name
     * @param type $site_id
     * @return type
     * @since 1.0 2016-1-15 SoChishun Added.
     */
    function check_attr_name($field_name, $site_id) {
        return $this->where(array('field_name' => $field_name, 'site_id' => $site_id))->getField('title');
    }

    /**
     * 判断字段名称是否存在
     * @param type $title
     * @param type $site_id
     * @return type
     * @since 1.0 2016-1-15 SoChishun Added.
     */
    function check_attr_title($title, $site_id) {
        return $this->where(array('title' => $title, 'site_id' => $site_id))->getField('title');
    }

    /**
     * 自动建立当前租户的系统字段属性
     * 每个租户创建的时候默认都会复制一份系统客户表的属性副本到租户归属中
     * @param type $site_id
     * @return type
     * @since 1.0 2016-1-15 SoChishun Added.
     */
    function generate_default_fields($site_id = 0) {
        $n = $this->where(array('site_id' => $site_id))->count();
        if ($n > 0) {
            return;
        }
        // 
        /* 可用此语句构建
          -- 创建临时表 2016-1-15 SoChishun Added.
          create temporary table temp_customer select
          COLUMN_COMMENT as title, '基本资料' as group_name, COLUMN_NAME as field_name, DATA_TYPE as field_type, IFNULL(CHARACTER_MAXIMUM_LENGTH,'') as field_length, IFNULL(NUMERIC_SCALE,'') as field_decimals, IFNULL(COLUMN_DEFAULT,'') as field_default, COLUMN_COMMENT as help_text, LEFT(IS_NULLABLE,1) as is_form, 'Y' as is_editable, DATA_TYPE as options_input_type, COLUMN_COMMENT as options_content
          from information_schema.`COLUMNS`
          where TABLE_NAME='t_porg_customer' and TABLE_SCHEMA='db_xwam_v1' and COLUMN_NAME not in ('id','site_id','','','');

          -- 查询临时表
          select * from temp_customer;
          select concat(',(\'',title,'\', \'',group_name,'\', \'',field_name,'\', \'',field_type,'\', \'',field_length,'\', \'',field_decimals,'\', \'',field_default,'\', \'',help_text,'\', \'',is_form,'\', \'',options_input_type,'\', \'',options_content,'\')') from temp_customer;
          -- 格式化配置内容
          update temp_customer set options_content=SUBSTR(title,POSITION('(' IN title)+1) where title like '%(%';
          update temp_customer set options_content='' where options_content not like '%;%';
          update temp_customer set options_content=REPLACE(options_content,')','') where options_content <> '';
          -- 格式化标题
          update temp_customer set title=SUBSTR(title,1,POSITION('(' IN title)-1) where title like '%(%';
          update temp_customer set title=SUBSTR(title,POSITION('/' IN title)+1) where title like '%/%';
          -- 格式化帮助提示
          update temp_customer set help_text='' where field_name <> 'labels';
          update temp_customer set help_text=SUBSTR(help_text,POSITION('(' IN help_text)+1) where help_text <> '';
          update temp_customer set help_text=REPLACE(help_text,')','') where help_text <> '';
          -- 修正系统特殊属性
          update temp_customer set field_default='', options_input_type='datetime' where field_default='CURRENT_TIMESTAMP';
          update temp_customer set is_editable='N' where field_name in ('create_time', 'user_name', 'old_user_name');
          update temp_customer set options_input_type='text' where field_type in ('varchar','char');
          update temp_customer set options_input_type='int' where field_type = 'smallint';
          update temp_customer set options_input_type='select' where field_name in ('sex','id_type','vip_level','interested','contact_result');
          update temp_customer set options_input_type='textarea' where field_name in ('remark');
          update temp_customer set options_input_type='file' where field_name in ('face_url');
          -- 删除临时表
          drop table if exists temp_customer;
         */
        /*
         * 2016-3-24 取消固定字段
         * ('客户编号', '基本资料', 'serial_no', 'varchar', '32', '', '', '', 'N', 'text', '')
          ,('客户姓名', '基本资料', 'name', 'varchar', '16', '', '', '', 'N', 'text', '')
          ,('性别', '基本资料', 'sex', 'varchar', '6', '', '未知', '', 'N', 'select', '女;男;未知')
          ,('电话号码', '基本资料', 'telphone', 'varchar', '32', '', '', '', 'N', 'text', '')
          ,('联系地址', '基本资料', 'address', 'varchar', '200', '', '', '', 'N', 'text', '')
          ,('工号', '基本资料', 'user_name', 'varchar', '16', '', '', '', 'N', 'text', '')
          ,('原工号', '基本资料', 'old_user_name', 'varchar', '16', '', '', '', 'N', 'text', '')
          ,('状态', '基本资料', 'status', 'smallint', '', '0', '0', '', 'N', 'int', '0:未审核;1:正常;3:冻结;4:禁用;7:注销;14:回收站')
          ,('备注', '基本资料', 'remark', 'varchar', '64', '', '', '', 'N', 'textarea', '')
          ,('创建时间', '基本资料', 'create_time', 'timestamp', '', '', '', '', 'N', 'datetime', '')
          ,('推荐人用户编号', '基本资料', 'referrer_user_id', 'int', '', '0', '0', '', 'int', '')
         * 
         */
        $sql = 'insert into t_porg_customer_attr (title, group_name, field_name, field_type, field_length, field_decimals, field_default, field_script, help_text, options_input_type, options_content, site_id) values ';
        $sql.="('用户头像', '基本资料', 'face_url', 'varchar', '255', '', '', 'face_url varchar(255) not null default \'\'', '', 'file', '', $site_id)
,('婚姻状况', '基本资料', 'marital_status', 'varchar', '9', '', '', 'marital_status varchar(9) not null default \'\'', '', 'text', '', $site_id)
,('生日', '基本资料', 'birthday', 'datetime', '', '', '', 'birthday datetime not null default 0', '', 'datetime', '', $site_id)
,('证件类型', '基本资料', 'id_type', 'varchar', '16', '', '', 'id_type varchar(16) not null default \'\'', '', 'select', '身份证;学生证;工作证;士兵证;军官证;护照', $site_id)
,('证件号码', '基本资料', 'id_no', 'varchar', '32', '', '', 'id_no varchar(32) not null default \'\'', '', 'text', '', $site_id)
,('邮政编码', '基本资料', 'zip', 'varchar', '18', '', '', 'zip varchar(18) not null default \'\'', '', 'text', '', $site_id)
,('客户等级', '基本资料', 'vip_level', 'varchar', '16', '', '', 'vip_level varchar(16) not null default \'\'', '', 'select', '普通;铜卡;银卡;金卡', $site_id)
,('消费次数', '基本资料', 'buy_count', 'int', '', '0', '0', 'buy_count int not null default 0', '', 'int', '', $site_id)
,('累计积分', '基本资料', 'integral', 'int', '', '0', '0', 'integral int not null default 0', '', 'int', '', $site_id)
,('累计消费金额', '基本资料', 'total_amount', 'decimal', '', '2', '0.00', 'total_amount decimal(8,2) not null default 0', '', 'decimal', '', $site_id)
,('最后消费时间', '基本资料', 'buy_time', 'datetime', '', '', '', 'buy_time datetime not null default 0', '', 'datetime', '', $site_id)
,('最后联系时间', '基本资料', 'review_time', 'datetime', '', '', '', 'review_time datetime not null default 0', '', 'datetime', '', $site_id)
,('购买意向', '基本资料', 'interested', 'varchar', '4', '', '', 'interested varchar(4) not null default \'\'', '', 'select', '无;意向客户;特别兴趣', $site_id)
,('联系结果', '基本资料', 'contact_result', 'varchar', '16', '', '', 'contact_result varchar(16) not null default \'\'', '', 'select', '拒绝;成功;对方为老人或小孩;接通后即结束通话;接通后不说话', $site_id)
,('客户来源', '基本资料', 'source', 'varchar', '16', '', '', 'source varchar(16) not null default \'\'', '', 'text', '客资导入;手动添加;来电弹屏;QQ营销', $site_id)
,('标注标签', '基本资料', 'labels', 'varchar', '32', '', '', 'labels varchar(32) not null default \'\'', '多个值以英文逗号隔开', 'text', '', $site_id)";
        $this->execute($sql);
        $this->execute('update ' . $this->tableName . ' set sort=id'); // 更新排序字段
        $this->where(array('field_name' => array('in', array('serial_no', 'name', 'sex', 'telphone', 'address', 'contact_result', 'user_name', 'labels', 'create_time'))))->setField(array('is_list' => 'Y')); // 列表字段
        $this->where(array('field_name' => array('in', array('serial_no', 'name', 'telphone', 'address', 'labels', 'create_time'))))->setField(array('is_search' => 'Y')); // 搜索字段
        $this->where(array('field_name' => array('in', array('name', 'mobile', 'address'))))->setField(array('is_export' => 'Y')); // 导出字段
        $this->where(array('field_name' => array('in', array('name', 'mobile', 'address'))))->setField(array('is_import' => 'Y')); // 导入字段
        $this->where(array('field_name' => array('in', array('serial_no', 'name', 'sex', 'telphone', 'address', 'interested', 'contact_result', 'labels', 'remark'))))->setField(array('is_form' => 'Y')); // 表单字段
        $this->where(array('field_name' => array('in', array('serial_no', 'name', 'sex', 'telphone', 'address', 'interested', 'contact_result', 'labels', 'remark'))))->setField(array('is_popup' => 'Y')); // 来电弹屏字段
    }

}
